package model;

import java.io.FileNotFoundException;
import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.LinkedList;
import java.util.List;

import javax.naming.NamingException;
import javax.sql.RowSet;
import javax.sql.rowset.CachedRowSet;

import com.sun.rowset.CachedRowSetImpl;

import db.DbConnectionPool;
import db.DbException;
import forms.ApplicantForm;
import forms.Degree;
import forms.ReviewerForm;
import model.ApplicationModel;


public class ReviewerModel {
	private static String selectRev = "SELECT user_ref, user_name FROM user_roles WHERE user_roles.role = 'reviewer'";
	
	private static String selectOtherRev = "SELECT user_ref, user_name FROM user_roles WHERE user_roles.role = 'reviewer' AND user_roles.user_name != ?";

	
	private static String insertRev = "INSERT INTO users (user_name,password) VALUES (?, md5(?))";
	private static String insertRevRol = "INSERT INTO user_roles(user_ref, user_name, role) VALUES (?, ?, ?)";
	
	private static String deleteRev = "DELETE FROM users WHERE id = ?";
	private static String deleteRevRol = "DELETE FROM user_roles WHERE user_ref = ?";
	
	private static String selectAvgGrade = "SELECT AVG(grade) AS avg_grade"
				+ " FROM applications"
				+ " WHERE application = ?";
	
	
	public static double getAvgGrade(int appID) throws DbException{
		try{
			Connection conn = DbConnectionPool.getConnection();

			// Create the statement
			PreparedStatement pStmt = conn.prepareStatement(selectAvgGrade);
			pStmt.setInt(1, appID);
	
			// Use the created statement to SELECT
			// the student attributes FROM the Student table.
			ResultSet avgGrade = pStmt.executeQuery();
	
			CachedRowSet crsAvgGrade = new CachedRowSetImpl();
			crsAvgGrade.populate(avgGrade);
			crsAvgGrade.next();
			double theAvgGrade = crsAvgGrade.getDouble("avg_grade");
	
			avgGrade.close();
			pStmt.close();
			conn.close();
	
			return theAvgGrade;
		} catch (SQLException ex) {
			throw new DbException(ex);
		} catch (NamingException ex) {
			throw new DbException(ex);
		}
	}
	
	public static CachedRowSet getOtherReviewers(String reviewer) throws DbException{
		try{
			Connection conn = DbConnectionPool.getConnection();

			// Create the statement
			PreparedStatement pStmt = conn.prepareStatement(selectOtherRev);
			pStmt.setString(1, reviewer);
	
			// Use the created statement to SELECT
			// the student attributes FROM the Student table.
			ResultSet allOtherReviewers = pStmt.executeQuery();
	
			CachedRowSet crsReviewers = new CachedRowSetImpl();
			crsReviewers.populate(allOtherReviewers);
	
			allOtherReviewers.close();
			pStmt.close();
			conn.close();
	
			return crsReviewers;
		} catch (SQLException ex) {
			throw new DbException(ex);
		} catch (NamingException ex) {
			throw new DbException(ex);
		}
	}
	
	public static CachedRowSet getAppGraded(String reviewer) throws DbException
	{
		try {
			
			Connection conn = DbConnectionPool.getConnection();
			
			PreparedStatement pStmt = conn.prepareStatement("SELECT user_ref FROM user_roles WHERE user_roles.role = 'reviewer' AND user_name='"+reviewer+"'");
			ResultSet reviewerID = pStmt.executeQuery();
			reviewerID.next();
			int rID = reviewerID.getInt("user_ref");
			
			pStmt = conn.prepareStatement("SELECT stu.first_name, stu.middle_initial, stu.last_name"
					+" FROM students stu, users, reviewers_apps ra"
					+" WHERE users.id = stu.id"
						+" AND users.id = ra.application"
						+" AND ra.graded = 'true'"
						+" AND ra.reviewer = '"+rID+"'");
		
			ResultSet AppsGraded = pStmt.executeQuery();
			CachedRowSet crsAppsGraded = new CachedRowSetImpl();
			crsAppsGraded.populate(AppsGraded);
			
			pStmt.close();
			reviewerID.close();
			AppsGraded.close();
			
			return crsAppsGraded;
		} catch (SQLException ex) {
			throw new DbException(ex);
		} catch (NamingException ex) {
			throw new DbException(ex);
		}
	}
	
	public static CachedRowSet getAppUngraded(String reviewer) throws DbException
	{
		try {
			
			Connection conn = DbConnectionPool.getConnection();
			
			PreparedStatement pStmt = conn.prepareStatement("SELECT user_ref FROM user_roles WHERE user_roles.role = 'reviewer' AND user_name='"+reviewer+"'");
			ResultSet reviewerID = pStmt.executeQuery();
			reviewerID.next();
			int rID = reviewerID.getInt("user_ref");
			
			pStmt = conn.prepareStatement("SELECT stu.first_name, stu.middle_initial, stu.last_name"
					+" FROM students stu, users, reviewers_apps ra"
					+" WHERE users.id = stu.id"
						+" AND users.id = ra.application"
						+" AND ra.graded = 'false'"
						+" AND ra.reviewer = '"+rID+"'");
		
			ResultSet AppsGraded = pStmt.executeQuery();
			CachedRowSet crsAppsGraded = new CachedRowSetImpl();
			crsAppsGraded.populate(AppsGraded);
			
			pStmt.close();
			reviewerID.close();
			AppsGraded.close();
			
			
			return crsAppsGraded;
		} catch (SQLException ex) {
			throw new DbException(ex);
		} catch (NamingException ex) {
			throw new DbException(ex);
		}
	}


	public static CachedRowSet getReviewersData() throws DbException
	{
		try {
			
			Connection conn = DbConnectionPool.getConnection();
			
			PreparedStatement pStmt = conn.prepareStatement("SELECT DISTINCT ur.user_name, count(ra.graded) as graded_count, 0 as ungraded_count"
				+" FROM reviewers_apps ra, user_roles ur"
				+" WHERE ur.role = 'reviewer'"
					+" AND ra.reviewer = ur.id"
					+" AND graded = 'true'"
				+" GROUP BY user_name"
				+" UNION"
				+" SELECT DISTINCT ur.user_name, 0 as count, count(ra.graded) as ungraded_count"
				+" FROM reviewers_apps ra, user_roles ur"
				+" WHERE ur.role = 'reviewer'"
					+" AND ra.reviewer = ur.id"
					+" AND graded = 'false'"
				+" GROUP BY user_name;");
			
			ResultSet reviewersInfo = pStmt.executeQuery();
			CachedRowSet crsReviewersInfo = new CachedRowSetImpl();
			crsReviewersInfo.populate(reviewersInfo);
			
			
			pStmt.close();
			reviewersInfo.close();
			

			return crsReviewersInfo;
		} catch (SQLException ex) {
			throw new DbException(ex);
		} catch (NamingException ex) {
			throw new DbException(ex);
		}
	}
	
	
	public static CachedRowSet getApps(String reviewer) throws DbException
	{
		try {
			Connection conn = DbConnectionPool.getConnection();
			
			PreparedStatement pStmt = conn.prepareStatement("SELECT user_ref FROM user_roles WHERE user_roles.role = 'reviewer' AND user_name='"+reviewer+"'");
			ResultSet reviewerID = pStmt.executeQuery();
			reviewerID.next();
			int rID = reviewerID.getInt("user_ref");
			
			pStmt = conn.prepareStatement("SELECT application from reviewers_apps WHERE reviewer ='"+rID+"' AND graded = 'false'");
			ResultSet allApps = pStmt.executeQuery();
			
			CachedRowSet crsAppsID = new CachedRowSetImpl();
			crsAppsID.populate(allApps);
			
			
			allApps.close();
			pStmt.close();
			conn.close();

			return crsAppsID;
		} catch (SQLException ex) {
			throw new DbException(ex);
		} catch (NamingException ex) {
			throw new DbException(ex);
		}
	}
	
	public static CachedRowSet getAllReviewers() throws DbException 
	{
		try {
			Connection conn = DbConnectionPool.getConnection();

			// Create the statement
			PreparedStatement pStmt = conn.prepareStatement(selectRev);

			// Use the created statement to SELECT
			// the student attributes FROM the Student table.
			ResultSet allReviewers = pStmt.executeQuery();

			CachedRowSet crsReviewers = new CachedRowSetImpl();
			crsReviewers.populate(allReviewers);

			allReviewers.close();
			pStmt.close();
			conn.close();

			return crsReviewers;
		} catch (SQLException ex) {
			throw new DbException(ex);
		} catch (NamingException ex) {
			throw new DbException(ex);
		}
	}
	
	public static void insertReviewer(ReviewerForm reviewer) throws DbException 
	{
		try {
			Connection conn = DbConnectionPool.getConnection();

			// Create the prepared statement and use it to
			// INSERT student values INTO the Students table.
			PreparedStatement pStmt = conn.prepareStatement(insertRev);

			pStmt.setString(1, reviewer.getUsername());
			pStmt.setString(2, reviewer.getPassword());
			pStmt.executeUpdate();

			// Commit transaction
			conn.commit();
			
			PreparedStatement pStmt3 = conn.prepareStatement("SELECT * FROM users WHERE user_name='" + reviewer.getUsername() + "'");
			ResultSet getUserId = pStmt3.executeQuery();
			
			getUserId.next();
			
			int userID = getUserId.getInt("id");
			
			PreparedStatement pStmt2 = conn.prepareStatement(insertRevRol);
			pStmt2.setInt(1, userID);
			pStmt2.setString(2, reviewer.getUsername());
			pStmt2.setString(3, "reviewer");
			pStmt2.executeUpdate();
			
			conn.commit();
			

			pStmt.close();
			pStmt2.close();
			conn.close();
		} catch (SQLException ex) {
			throw new DbException(ex);
		} catch (NamingException ex) {
			throw new DbException(ex);
		}
	}
	
	public static void deleteReviewer(ReviewerForm reviewer) throws DbException 
	{
		try {
			Connection conn = DbConnectionPool.getConnection();

			// Create the prepared statement and use it to
			// DELETE students FROM the Students table.
			PreparedStatement pStmt = conn.prepareStatement(deleteRev);
			PreparedStatement pStmt2 = conn.prepareStatement(deleteRevRol);

			pStmt2.setInt(1, reviewer.getId());
			pStmt2.executeUpdate();
			
			// Commit transaction
			conn.commit();

			pStmt.setInt(1, reviewer.getId());
			pStmt.executeUpdate();
			conn.commit();
			
			

			pStmt.close();
			pStmt2.close();
			conn.close();
		} catch (SQLException ex) {
			throw new DbException(ex);
		} catch (NamingException ex) {
			throw new DbException(ex);
		}
	}
	
	public static CachedRowSet getReviewerCount() throws DbException {

		try {
			Connection conn = DbConnectionPool.getConnection();

			// Create the statement
			PreparedStatement pStmt = conn.prepareStatement("SELECT COUNT(user_name) FROM user_roles WHERE role='reviewer'");

			// Use the created statement to SELECT
			// the student attributes FROM the Student table.
			ResultSet countReviewers = pStmt.executeQuery();

			CachedRowSet crsCountReviewers = new CachedRowSetImpl();
			crsCountReviewers.populate(countReviewers);

			countReviewers.close();
			pStmt.close();
			conn.close();

			return crsCountReviewers;
			
		} catch (SQLException ex) {
			throw new DbException(ex);
		} catch (NamingException ex) {
			throw new DbException(ex);
		}
	}
	
	public static void linkReviewerApps() throws DbException {

		try {
			Connection conn = DbConnectionPool.getConnection();

			// Create the statement
			PreparedStatement pStmt = conn.prepareStatement("INSERT INTO reviewers_apps (reviewer, application, graded) VALUES (?,?, 'false')");

			// Use the created statement to SELECT
			// the student attributes FROM the Student table.
			
			PreparedStatement pStmt2 = conn.prepareStatement("SELECT user_ref FROM user_roles WHERE role = 'reviewer'");
			PreparedStatement pStmt3 = conn.prepareStatement("SELECT appid FROM users WHERE status = 'submitted'");

			ResultSet reviewers = pStmt2.executeQuery(); 	
			ResultSet applications = pStmt3.executeQuery();
			
			while(applications.next()){
				if (reviewers.isLast() == true){
					pStmt2 = conn.prepareStatement("SELECT user_ref FROM user_roles WHERE role = 'reviewer'");
					reviewers = pStmt2.executeQuery(); 	
					reviewers.next();
					pStmt.setInt(1, reviewers.getInt("user_ref"));
					pStmt.setInt(2, applications.getInt("appid"));
					pStmt.executeUpdate();
				}else{
				reviewers.next();
				int user_ref = reviewers.getInt("user_ref");
				pStmt.setInt(1, user_ref);
				pStmt.setInt(2, applications.getInt("appid"));
				pStmt.executeUpdate();
				}
			}
			
			conn.commit();

			reviewers.close();
			applications.close();
			pStmt2.close();
			pStmt.close();
			conn.close();

			
		} catch (SQLException ex) {
			throw new DbException(ex);
		} catch (NamingException ex) {
			throw new DbException(ex);
		}
	}
	
	public static boolean determineStart() throws DbException {

		try {
			Connection conn = DbConnectionPool.getConnection();

			// Create the statement
			PreparedStatement pStmt = conn.prepareStatement("SELECT * FROM reviewers_apps");

			// Use the created statement to SELECT
			// the student attributes FROM the Student table.
			ResultSet status = pStmt.executeQuery();
			
			if (status.next()){
				status.close();
				pStmt.close();
				conn.close();
				return true;
			}else{
				status.close();
				pStmt.close();
				conn.close();
				return false;
			}
			

		} catch (SQLException ex) {
			throw new DbException(ex);
		} catch (NamingException ex) {
			throw new DbException(ex);
		}
	}

}
